package com.xzk.ExpressInnWeb.dao.daoImp;

import com.xzk.ExpressInnWeb.bean.Courier;
import com.xzk.ExpressInnWeb.dao.BaseCourierDao;
import com.xzk.ExpressInnWeb.util.CourierUtil;
import com.xzk.ExpressInnWeb.util.DruidUtil;
import com.xzk.ExpressInnWeb.util.ParamType;
import com.xzk.ExpressInnWeb.util.QueryUtil;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class CourierDaoMySQL implements BaseCourierDao {

    private static final String SQL_FIND_COURIER_BY_ID = "SELECT * FROM COURIER WHERE ID=?";
    private static final String SQL_CONSOLE_COURIER = "SELECT COUNT(ID) AS SIZE, COUNT(TO_DAYS(CREATETIME)=TO_DAYS(NOW()) OR NULL) AS DAY FROM COURIER";
    private static final String SQL_FIND_ALL_COURIER = "SELECT * FROM COURIER";
    private static final String SQL_FIND_ALL_COURIER_WITH_LIMIT = "SELECT * FROM COURIER LIMIT ? OFFSET ?";
    private static final String SQL_FIND_COURIER_BY_PHONE_NUM = "SELECT * FROM COURIER WHERE STAFFPHONE=?";
    private static final String SQL_INSERT_COURIER = "INSERT INTO COURIER (COURIERNAME,STAFFPHONE,PERSONALID,PASSWORD,CREATETIME) VALUES(?,?,?,?,NOW())";
    private static final String SQL_UPDATE_COURIER = "UPDATE COURIER SET COURIERNAME=?,STAFFPHONE=?,PERSONALID=?,PASSWORD=? WHERE ID=?";
    private static final String SQL_DELETE_COURIER = "DELETE FROM COURIER WHERE ID=?";
    private static final String SQL_UPDATE_LOGIN_TIME = "UPDATE COURIER SET LOGINTIME=? WHERE STAFFPHONE=?";
    private static final String SQL_COURIER_LOGIN = "SELECT ID FROM COURIER WHERE STAFFPHONE=? AND PASSWORD=?";
    private static final String SQL_UPDATE_DELIVERY = "UPDATE COURIER SET DELIVERY=DELIVERY+1 WHERE STAFFPHONE=?";

    private Connection conn = null;
    private PreparedStatement state = null;
    private ResultSet rs = null;

    private <T> Courier findCourierBySingleValue(String sql, T param){
        Courier courier = null;
        rs = QueryUtil.modifyResultSet(sql,param,conn,state,rs);
        if (rs != null){
            try {
                if (rs.next()) {
                    courier = CourierUtil.buildCourier(rs);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                DruidUtil.close(conn,state,rs);
            }
        }
        return courier;
    }

    @Override
    public Courier findCourierByID(int id) {
        return findCourierBySingleValue(SQL_FIND_COURIER_BY_ID, id);
    }

    @Override
    public Map<String, Integer> consoleCourier() {
        return QueryUtil.consoleOutput(SQL_CONSOLE_COURIER,conn,state,rs);
    }

    @Override
    public List<Courier> findAllCourier(boolean split, int offset, int limit) {
        ArrayList<Courier> data = new ArrayList<>();
        if (split) {
            rs = QueryUtil.query(
                    SQL_FIND_ALL_COURIER_WITH_LIMIT,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.INT,limit),
                            QueryUtil.getMap(ParamType.INT,offset)
                    ),
                    state,conn
            );
        } else {
            rs = QueryUtil.query(SQL_FIND_ALL_COURIER,null,state,conn);
        }
        try {
            while (rs.next()) {
                data.add(CourierUtil.buildCourier(rs));
            }
        } catch (SQLException|NullPointerException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,rs);
        }
        return data;
    }

    @Override
    public Courier findCourierByPhoneNum(String staffphone) {
        return findCourierBySingleValue(SQL_FIND_COURIER_BY_PHONE_NUM, staffphone);
    }

    @Override
    public boolean insertCourier(Courier courier) {
        String couriername = courier.getCouriername();
        String staffphone = courier.getStaffphone();
        String personalid = courier.getPersonalid();
        String password = courier.getPassword();
        boolean result = false;
        try {
            result = QueryUtil.queryDML(
                    SQL_INSERT_COURIER,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.STRING,couriername),
                            QueryUtil.getMap(ParamType.STRING,staffphone),
                            QueryUtil.getMap(ParamType.STRING,personalid),
                            QueryUtil.getMap(ParamType.STRING,password)
                    ),
                    state,conn
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean updateCourier(int id, Courier courier) {
        String couriername = courier.getCouriername();
        String staffphone = courier.getStaffphone();
        String personalid = courier.getPersonalid();
        String password = courier.getPassword();
        boolean result = false;
        try {
            result = QueryUtil.queryDML(
                    SQL_UPDATE_COURIER,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.STRING,couriername),
                            QueryUtil.getMap(ParamType.STRING,staffphone),
                            QueryUtil.getMap(ParamType.STRING,personalid),
                            QueryUtil.getMap(ParamType.STRING,password),
                            QueryUtil.getMap(ParamType.INT,id)),
                    state, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public void updateDelivery(String staffphone) {
        try {
            QueryUtil.queryDML(
                    SQL_UPDATE_DELIVERY,
                    QueryUtil.getList(QueryUtil.getMap(ParamType.STRING,staffphone)),
                    state,conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public boolean deleteCourier(int id) {
        boolean result = false;
        try {
            result = QueryUtil.queryDML(
                    SQL_DELETE_COURIER,
                    QueryUtil.getList(QueryUtil.getMap(ParamType.INT, id)),
                    state, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public void updateLoginTime(String staffphone, Date date) {
        try {
            QueryUtil.queryDML(
                    SQL_UPDATE_LOGIN_TIME,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.TIMESTAMP, new Timestamp(date.getTime())),
                            QueryUtil.getMap(ParamType.STRING,staffphone)),
                    state, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public boolean login(String staffphone, String password) {
        rs = QueryUtil.query(
                SQL_COURIER_LOGIN,
                QueryUtil.getList(
                        QueryUtil.getMap(ParamType.STRING,staffphone),
                        QueryUtil.getMap(ParamType.STRING,password)
                ),state, conn);
        try {
            return rs.next();
        } catch (SQLException|NullPointerException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, rs);
        }
        return false;
    }
}
